﻿#region

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

#endregion

namespace DataAccessLayer
{
    public static class VehicleDAL
    {
        private static readonly string ConnectionString =
            ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

        /// <summary>
        ///     Register a vehicle
        /// </summary>
        /// <param name="vehicle">Represent's the vehicle</param>
        public static void RegisterVehicle(Vehicle vehicle)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spAddVehicle", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@RegistrationNo", vehicle.RegistrationNo);
                command.Parameters.AddWithValue("@SubsidiaryId", vehicle.SubsidiaryId);
                command.Parameters.AddWithValue("@SupervisorId", vehicle.SupervisorId);
                command.Parameters.AddWithValue("@Type", vehicle.Type);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Get All vehicles
        /// </summary>
        /// <returns>All vehicles</returns>
        public static List<Vehicle> GetAllVehicles()
        {
            List<Vehicle> vehicles = new List<Vehicle>();
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetAllVehicle", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Vehicle vehicle = new Vehicle
                        {
                            RegistrationNo = reader["RegistrationNo"].ToString(),
                            SupervisorId = Convert.ToInt32(reader["SupervisorId"]),
                            SubsidiaryId = Convert.ToByte(reader["SubsidiaryId"]),
                            Type = reader["Type"].ToString(),
                            Availability = Convert.ToBoolean(reader["Availability"])
                        };
                        vehicles.Add(vehicle);
                    }
                }
            }
            return vehicles;
        }

        /// <summary>
        ///     Get All vehicles by subsidiary id
        /// </summary>
        /// <param name="subsidiaryId">Represent's the subsidiary id</param>
        /// <returns>All vehicles</returns>
        public static List<Vehicle> GetAllVehicles(byte subsidiaryId)
        {
            List<Vehicle> vehicles = new List<Vehicle>();
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetVehiclesBySubsidiaryId", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@SubsidiaryId", subsidiaryId);

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Vehicle vehicle = new Vehicle
                        {
                            RegistrationNo = reader["RegistrationNo"].ToString(),
                            SupervisorId = Convert.ToInt32(reader["SupervisorId"]),
                            SubsidiaryId = subsidiaryId,
                            Type = reader["Type"].ToString(),
                            Availability = Convert.ToBoolean(reader["Availability"])
                        };
                        vehicles.Add(vehicle);
                    }
                }
            }
            return vehicles;
        }

        /// <summary>
        ///     Check whether the vehicle already registered
        /// </summary>
        /// <param name="regNo">Represent's the vehicle registration number</param>
        /// <returns>Registered or not</returns>
        public static bool IsAlreadyRegistered(string regNo)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spIsVehicleRegistrationNoExist", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@RegistrationNo", regNo);

                connection.Open();
                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        /// <summary>
        ///     Edit vehicle's details
        /// </summary>
        /// <param name="vehicle">Represent's the vehicle</param>
        public static void EditVehicle(Vehicle vehicle)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spEditVehicle", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@RegistrationNo", vehicle.RegistrationNo);
                command.Parameters.AddWithValue("@SubsidiaryId", vehicle.SubsidiaryId);
                command.Parameters.AddWithValue("@SupervisorId", vehicle.SupervisorId);
                command.Parameters.AddWithValue("@Type", vehicle.Type);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Get vehicle by registration number
        /// </summary>
        /// <returns>Vehicle</returns>
        public static Vehicle GetVehiclesByRegNo(string registrationNo)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetVehicleByRegNo", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@RegistrationNo", registrationNo);

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        Vehicle vehicle = new Vehicle
                        {
                            RegistrationNo = registrationNo,
                            SupervisorId = Convert.ToInt32(reader["SupervisorId"]),
                            SubsidiaryId = Convert.ToByte(reader["SubsidiaryId"]),
                            Type = reader["Type"].ToString(),
                            Availability = Convert.ToBoolean(reader["Availability"])
                        };
                        return vehicle;
                    }
                }
            }
            return null;
        }

        /// <summary>
        ///     Remove a vehicle
        /// </summary>
        /// <param name="regNo">Represent's the vehicle registration number</param>
        public static void RemoveVehicle(string regNo)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spRemoveVehicle", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@VehicleRegNo", regNo);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Set vehicle's availability (True / False)
        /// </summary>
        /// <param name="vehicle">Represent's the vehicle</param>
        public static void SetAvailability(Vehicle vehicle)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spSetVehicleAvailability", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@RegistrationNo", vehicle.RegistrationNo);
                command.Parameters.AddWithValue("@Availability", vehicle.Availability);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Get All available vehicles by types for booking
        /// </summary>
        /// <param name="subsidiaryId">Represent's the subsidiary id</param>
        /// <param name="type">Represent's the vehicle type (Van / Car)</param>
        /// <param name="pickupDateTime">Represent's the pickup date and time</param>
        /// <returns>Vehicles</returns>
        public static List<Vehicle> GetAllVehicles(byte subsidiaryId, string type, DateTime pickupDateTime)
        {
            List<Vehicle> vehicles = new List<Vehicle>();
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetAllVehiclesAvailableForBooking", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@SubsidiaryId", subsidiaryId);
                command.Parameters.AddWithValue("@Type", type);
                command.Parameters.AddWithValue("@PickupDateTime", pickupDateTime);

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Vehicle vehicle = new Vehicle
                        {
                            RegistrationNo = reader["RegistrationNo"].ToString(),
                            SupervisorId = Convert.ToInt32(reader["SupervisorId"]),
                            SubsidiaryId = subsidiaryId,
                            Type = type,
                            Availability = Convert.ToBoolean(reader["Availability"])
                        };
                        vehicles.Add(vehicle);
                    }
                }
            }
            return vehicles;
        }
    }
}